MySQL CheatSheet

August 10, 2023

Here's a MySQL cheat sheet with some commonly used commands and concepts:

Connecting to MySQL:

mysql -u username -p

Basic Commands:

Show Databases:

SHOW DATABASES;

Create Database:

CREATE DATABASE database_name;

Use Database:

USE database_name;

Show Tables:

Show Tables

Describe Table:

DESCRIBE table_name;

Data Manipulation:

Insert Data:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Update Data:

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

Delete Data:

DELETE FROM table_name WHERE condition;

Select Data:

SELECT column1, column2, ... FROM table_name WHERE condition;

Filtering and Sorting:

Filtering with WHERE:

SELECT * FROM table_name WHERE condition;

Sorting with ORDER BY:

SELECT * FROM table_name ORDER BY column_name ASC/DESC;

Aggregation Functions:

Count:

SELECT COUNT(column_name) FROM table_name;

Sum:

SELECT SUM(column_name) FROM table_name;

Average:

SELECT AVG(column_name) FROM table_name;

Joining Tables:

Inner Join:

SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

Left Join:

SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

Grouping Data:

SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

Indexes:

Creating Index:

CREATE INDEX index_name ON table_name(column_name);

Dropping Index:

DROP INDEX index_name ON table_name;

Backup and Restore:

Backup:

mysqldump -u username -p database_name > backup.sql

Restore:

SHOW DATABASES;

Remember to replace username, database_name, table_name, etc., with your actual values. This cheat sheet covers the basics, but MySQL is a rich and powerful database system, so there's a lot more you can explore and learn.